package util.mysql;

import util.ActConf;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Function;

/**
 * Mysql查询的一个工具 返回要给Map 别名为key
 * <p>
 * Created by J.Chen on 2017/5/19.
 */
public class MysqlUtil {
    private Connection conn;

    public MysqlUtil(Connection conn) {
        this.conn = conn;
    }

    public List<Map<String, String>> selectList(String sql) {
        List<Map<String, String>> list = new ArrayList<>();
        execute(sql, (r) -> {
            try {
                ResultSetMetaData metaData = r.getMetaData();
                while (r.next()) {
                    Map<String, String> result = new HashMap<>();
                    for (int i = 0; i < metaData.getColumnCount(); i++) {
                        // resultSet数据下标从1开始
                        String columnName = metaData.getColumnLabel(i + 1);
                        result.put(columnName, r.getString(i + 1));
                    }
                    list.add(result);
                }
            } catch (Exception e) {

            }
            return null;
        });
        return list;
    }

    public  Map<String, String> selectOne(String sql) {
        List<Map<String, String>> list = selectList(sql);
        return list != null && list.size() > 0 ? list.get(0) : new HashMap<>();
    }

    private  <T> void execute(String sql, Function<ResultSet, T> function) {
        if (sql != null) {
            String searchStart = "select";
            String value = sql.trim().substring(0, searchStart.length());
            if (!value.equalsIgnoreCase(searchStart)) {
                throw new RuntimeException("the sql is not a searchSql:" + sql);
            }
        }
        System.out.println("execute sql start:" + sql);
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = conn.createStatement();
            resultSet = statement.executeQuery(sql);
            function.apply(resultSet);
        } catch (Exception e) {
            System.err.println("execute sql error:" + sql);

        } finally {
            try {
                statement.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }

    public static String getLogActSql(String sql, String groupby, ActConf actConf, String func, int ret, String ext, String extSql, String orderBy) {
        String value = "SELECT %s FROM log_act WHERE create_time >= '%s' AND create_time <= '%s' AND conf_code = '%s' AND ret = %d AND func = '%s' ";
        if (ext != null) {
            value = value.concat(" and ext = '").concat(ext).concat("'");
        }
        if (extSql != null) {
            value = value.concat(" and ").concat(extSql);
        }
        if (groupby != null) {
            value = value.concat(" group by ").concat(groupby);
        }
        if (orderBy != null) {
            value = value.concat(" order by ").concat(orderBy);
        }
        value = String.format(value, sql == null ? "*" : sql, actConf.getConf().get("timeStart"), actConf.getConf().get("timeEnd"), actConf.getConfCode(), ret, func);
        return value;
    }

    public static String getBusinessLogSql(String sql, String groupby, ActConf actConf, String func, int ret, String ext, String extSql, String orderBy) {
        String value = "SELECT %s FROM business_log WHERE create_time >= '%s' AND create_time <= '%s' AND conf_code = '%s' AND ret = %d AND func = '%s'";
        if (ext != null) {
            value = value.concat(" and ext = ").concat(ext);
        }
        if (extSql != null) {
            value = value.concat(" and ").concat(extSql);
        }
        if (groupby != null) {
            value = value.concat(" group by ").concat(groupby);
        }
        if (orderBy != null) {
            value = value.concat(" order by ").concat(orderBy);
        }
        value = String.format(value, sql == null ? "*" : sql, actConf.getConf().get("timeStart"), actConf.getConf().get("timeEnd"), actConf.getConfCode(), ret, func);
        return value;
    }

}
